"""
Copyright 2012 Wieser Dietmar, Margit Mutschlechner.

This file is part of Multimedia Social Networking Platform.

Multimedia Social Networking Platform is free software: 
you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Multimedia Social Networking Platform is distributed
in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Multimedia Social Networking Platform.  
If not, see <http://www.gnu.org/licenses/>.

"""

import traceback
from msnp.model.PTPModel import User, Group, UserGroup, \
    BackupContent, Genre
from msnp.logger import Logger
from msnp.service.network.model import CustodyModel
from msnp.model import PTPModel
from msnp.persistence.mysql import DBErrorHandler
from msnp.service import utils

########
#tables#
########
TABLE_USER = "user"
TABLE_GROUP = "group"
TABLE_USER_GROUP = "user_group"
TABLE_MEMBER_GROUP = "member_group"
TABLE_GROUP_GENRE = "group_genre"
TABLE_GENRE = "genre"
TABLE_USER_MESSAGE = "user_message"
TABLE_MESSAGE = "message"
TABLE_CHAT_HISTORY = "chat_history"

#########################
#columns for group table#
#########################
COLUMN_GROUP_GROUPNAME = "groupname"
COLUMN_GROUP_DISPLAYNAME = "displayname"
COLUMN_GROUP_OWNERNAME = "ownername"
COLUMN_GROUP_DESCRIPTION = "description"
COLUMN_GROUP_IS_ACTIVE = "is_active"

########################
#columns for user table#
########################
COLUMN_USER_USERNAME = "username"
COLUMN_USER_DISPLAYNAME = "displayname"
COLUMN_USER_PW = "pw"
COLUMN_USER_BACKUP_FROM = "backup_from"
COLUMN_USER_TIMESTAMP = 'timestamp'


##############################
#columns for user_group table#
##############################
COLUMN_UG_USERNAME = "username"
COLUMN_UG_GROUPNAME = "groupname"
COLUMN_UG_OWNERNAME = "ownername"

################################
#columns for member_group table#
################################
COLUMN_MG_MEMBERNAME = "membername"
COLUMN_MG_GROUPNAME = "groupname"
COLUMN_MG_OWNERNAME = "ownername"
COLUMN_MG_STATUS = "status"
COLUMN_MG_TIMESTAMP = "timestamp"


###############################
#columns for genre_group table#
###############################
COLUMN_GG_GROUPNAME = "groupname"
COLUMN_GG_OWNERNAME = "ownername"
COLUMN_GG_GENRE = "genre"

###########################
#columns for message table#
###########################
COLUMN_MSG_ID = "id"
COLUMN_MSG_KEY = "msgkey"
COLUMN_MSG_VALUE = "msgvalue"

################################
#columns for user_message table#
################################
COLUMN_UM_MSGID = "message_id"
COLUMN_UM_USERNAME = "username"

#########################
#columns for genre table#
#########################
COLUMN_GENRE_NAME = "name"

################################
#columns for chat_history table#
################################
COLUMN_CH_USERNAME = "username"
COLUMN_CH_SENDERNAME = "sendername"
COLUMN_CH_PARTNERNAME = "partnername"
COLUMN_CH_MESSAGE = "message"
COLUMN_CH_STATUS = "status"
COLUMN_CH_TIMESTAMP = "timestamp"


def cleanDatabase(database, cursor, schema):
    """Cleans the whole ptp database.
    
    Deletes all entries of all tables of the given schema.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema that should be cleaned.
    """
    try:
        query = "delete from " + schema + "." + TABLE_USER
        cursor.execute(query)
        query = "delete from " + schema + "." + TABLE_GROUP
        cursor.execute(query)
        query = "delete from " + schema + "." + TABLE_MESSAGE
        cursor.execute(query)
        query = "delete from " + schema + "." + TABLE_GENRE
        cursor.execute(query)
        query = "delete from " + schema + "." + TABLE_USER_GROUP
        cursor.execute(query)
        query = "delete from " + schema + "." + TABLE_MEMBER_GROUP
        cursor.execute(query)
        query = "delete from " + schema + "." + TABLE_GROUP_GENRE
        cursor.execute(query)
        query = "delete from " + schema + "." + TABLE_USER_MESSAGE
        cursor.execute(query)
        database.commit()
    except:
        Logger.info("Error: unable to clean ptp database")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
        
def dropTables(database, cursor, schema):
    """Drops all tables of the given schema.
    
    Args:
        database: A Connection object representing the database where the tables should be dropped.
        cursor: A Cursor object needed for the execution of the database queries.
    """
    try:
        query = "drop table if exists "
        query += schema + "." + TABLE_USER_MESSAGE + ", "
        query += schema + "." + TABLE_GROUP_GENRE + ", "
        query += schema + "." + TABLE_MEMBER_GROUP + ", "
        query += schema + "." + TABLE_USER_GROUP + ", "
        query += schema + "." + TABLE_MESSAGE + ", "
        query += schema + "." + TABLE_USER + ", "
        query += schema + "." + TABLE_GROUP + ", "
        query += schema + "." + TABLE_GENRE
        cursor.execute(query)
        database.commit()
    except:
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.DROP, traceback.format_exc())
    
def executeQuery(database, cursor, query):
    """Executes the given query on the given database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        query: A String containing the query to be executed.
    """
    try:
        cursor.execute(query)
        database.commit()
    except:
        Logger.info("error while executing query: " + query)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UNKNOWN, traceback.format_exc())


############
## BACKUP ##
############

# select

def selectBackup(database, cursor, schema, fromUsername):
    """Selects the specific backup belonging to the given user from the ptp database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        fromUsername: A String representing the user name from which the backup should be selected.
    
    Returns:
        A BackupContent object containing the backup of the given user stored in the ptp database is returned
            or None if an error occurred.
    """
    try:
        #get all users for backup
        users = __selectUsersForBackup(database, cursor, schema, fromUsername)
        userGroups = []
        groups = []
        alreadyInserted = False
        if users == None or len(users) == 0:
            backup = BackupContent(fromUsername, None, None, None, None)
        else:
            for user in users:
                username = user.getUsername()
                #get all groups for every user (group details, members...)
                groupsToAdd = selectGroupsForUsername(database, cursor, schema, username)
                for group in groupsToAdd:
                    groupname = group.getGroupname()
                    ownername = group.getOwnername()
                    # insert only if the group is not already inserted
                    for alreadyInsertedGroup in groups:
                        if alreadyInsertedGroup.getGroupname() == groupname and alreadyInsertedGroup.getOwnername() == ownername:
                            alreadyInserted = True
                    if alreadyInserted == False:
                        # delete details if fromUsername is not responsible for the owner
                        removeDetails = True
                        for u in users:
                            if u.getUsername() == ownername:
                                removeDetails = False
                        if removeDetails:
                            g = Group(groupname, ownername)
                            groups.append(g)
                        else:
                            groups.append(group)
                    ug = UserGroup(username, groupname, ownername)
                    userGroups.append(ug)
                    alreadyInserted = False
            #create backup from collected information
            backup = BackupContent(fromUsername, users, userGroups, groups, None)
        return backup
    except:
        Logger.info("Error: unable to select backups for name")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectBackupFromUsers(database, cursor, schema):
    """Selects all usernames, that have some backup content stored in the ptp database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        
    Returns:
        A list of Strings representing all usernames, that store some backup in the ptp database is returned,
            a list without any entries if there exists no such user or None if an error occurred.
    """
    try:
        query = __getQuerySelectBackupFromUsers(schema)
        cursor.execute(query)
        users = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            uName = result[0]
            users.append(uName)
        return users
    except:
        Logger.info("Error: unable to get all backup from users")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

# insert
         
def insertOrUpdateBackup(database, cursor, schema, backupContent, overwriteNoneBackupFrom):
    """Inserts the given backup into the ptp database.
    
    Inserts or updates first the backuped users, then the groups
    and finally the user-group relations.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        backupContent: A BackupContent object containing the backup to be inserted into the ptp database.
        overwriteNoneBackupFrom: True if the value None of the column backup_from should be overwritte, otherwise False.
    
    Returns:
        Returns True if the insert was successful, or False if an error occurred.
    """
    try:
        if backupContent != None:
            #insert users
            __insertOrUpdateBackupUser(database, cursor, schema, backupContent.getUsers(), backupContent.getFromUsername(), overwriteNoneBackupFrom, backupContent.getTimestamp())
            #insert groups
            __insertOrUpdateBackupGroups(database, cursor, schema, backupContent.getGroups())
            #insert user_group
            __insertOrUpdateBackupUserGroups(database, cursor, schema, backupContent.getUserGroups())
            database.commit()
            return True
        else:
            Logger.info("Error: backupContent is None in insertBackup")
    except:
        Logger.info("Error: unable to insert backup")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False


def __insertOrUpdateBackupUser(database, cursor, schema, users, backup_from, overwriteNoneBackupFrom, timestamp):
    """Inserts or updates the given users as backup for the given username into the ptp database.
    
    Inserts or updates the users together with the messages contained in the user objects
    if there is no such entry in the database or if the timestamp in the databse is older than the given one. 
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        users: A list of User objects containing all users that have to be inserted into the ptp database.
        backup_from: A String containing the name of the user that should be written into the column backup_from.
        overwriteNoneBackupFrom: True if the value None of the column backup_from should be overwritte, otherwise False.
        timestamp: The exact timestamp that has to be inserted into the column timestamp.
    
    Returns:
        Returns True if the insert was successful, or False if an error occurred.
        
    Raises:
        An Exception is raised if the insertion of the users was incorrect.
    """
    if users != None:
        for user in users:
            tmp = "None" if backup_from == None else backup_from
            #check if user is already in db
            userInDb = __selectUserForUsername(database, cursor, schema, user.getUsername())
            if timestamp == None:
                timestamp = 0.0
            if userInDb == None:
                Logger.info("  - insert into db fromusername " + tmp + " user " + user.getUsername())
                #add user
                user.setBackupFromUsername(backup_from)
                user.setTimestamp(timestamp)
                successful = insertUser(database, cursor, schema, user, False)
                if successful == False:
                    raise Exception('insertError', 'user ' + user.getUsername())
            else:
                update = False
                if userInDb.getBackupFromUsername() != None:
                    if userInDb.getTimestamp() <= timestamp:
                        Logger.info("  - update user fromusername " + tmp + " user " + user.getUsername() + " (" + str(userInDb.getTimestamp()) + "<=" + str(timestamp) + ")")
                        update = True
                    elif backup_from == None:
                        Logger.info("  - update user fromusername " + tmp + " user " + user.getUsername() + " (backup_from = None)")
                        update = True
                    else:
                        Logger.info("  - don't update user fromusername " + tmp + " user " + user.getUsername() + " 1. case - (" + str(userInDb.getTimestamp()) + "vs." + str(timestamp) + ")")
                elif userInDb.getBackupFromUsername() == None and overwriteNoneBackupFrom:
                    Logger.info("  - update user fromusername " + tmp + " user " + user.getUsername() + " (fromuser in db = None and overwriteNoneBackupFrom = True)")
                    update = True
                else:
                    Logger.info("  - don't update user fromusername " + tmp + " user " + user.getUsername() + " 2. case - (" + str(userInDb.getTimestamp()) + "vs." + str(timestamp) + ")")
                    
                if update:
                    query = __getQueryUpdateUser(schema)
#                    backup_from = "NULL" if(backup_from == None) else backup_from
                    cursor.execute(query, [user.getDisplayname(), user.getPassword(), backup_from, timestamp, user.getUsername()])
                    insertMessages(database, cursor, schema, user.getUsername(), user.getMessages(), False)

def __insertOrUpdateBackupGroups(database, cursor, schema, groups):
    """Inserts or updates the given groups into the ptp database.
    
    Inserts or updates the groups together with their members,
    the member-group relations and the genre-group relations.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        group: A list of Group objects containing all groups that have to be inserted into the ptp database.
    
    Returns:
        Returns True if the insert was successful, or False if an error occurred.
        
    Raises:
        An Exception is raised if the insertion was incorrect.
    """
    if groups != None:
        for group in groups:
            groupname = group.getGroupname()
            ownername = group.getOwnername()
            groupInDb = selectGroupForGroupnameAndOwnername(database, cursor, schema, groupname, ownername)
            #insert group if not already in db
            if groupInDb == None:
                isInserted = __insertGroup(database, cursor, schema, group)
                if isInserted != PTPModel.ReturnType.SUCCESSFUL:
                    raise Exception('insertError', 'group ' + group.getGroupname())
            #insert only groupdetails if group is already in db
            else:
                dName = group.getDisplayname() if (group.getDisplayname() != None) else  groupInDb.getDisplayname()
                description = group.getDescription() if (group.getDescription() != None) else groupInDb.getDescription()
                genres = group.getGenres()
                isActive = group.isActive() if (group.isActive() != None) else groupInDb.isActive()
                query = __getQueryUpdateGroupDetails(schema)
                cursor.execute(query, [dName, description, isActive, groupname, ownername])
                if genres != None and len(genres) > 0:
                    query = __getQueryDeleteGenresForGroupnameAndOwnername(schema)
                    cursor.execute(query, [groupname, ownername])
                    query = __getQueryInsertGenreGroup(schema)
                    for genre in genres:
                        cursor.execute(query, [groupname, ownername, genre.getName()])
                __insertOrUpdateBackupMemberGroup(database, cursor, schema, groupname, ownername, group.getMembers(), groupInDb)

def __insertOrUpdateBackupMemberGroup(database, cursor, schema, groupname, ownername, members, groupInDb):
    """Inserts or updates the given members into the ptp database.
    
    Inserts or updates the members together with their member-group relations.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        groupname: A String containing the group name which members have to be inserted into the ptp database.
        ownername: A String containing the owner name which members have to be inserted into the ptp database.
        members: A list of Member objects containing the group's members that have to be inserted into the ptp database.
        groupInDb: A Group object representing the corresponding group that is already in the database. 
    
    Returns:
        Returns True if the insert was successful, or False if an error occurred.
    """
    if groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0 and len(groupname) > 0 and members != None:
        for member in members:
            membername = member.getName()
            status = member.getStatus()
            timestamp = member.getTimestamp()
            # if member-group is not already in db it gets inserted
            if groupInDb.getMembers() == None or groupInDb.containsMember(membername) == False:
                query = __getQueryInsertMemberGroup(schema)
                cursor.execute(query, [membername, groupname, ownername, status, timestamp])
            # if member is already in db update status and timestamp if different
            else:
                query = __getQueryUpdateMemberGroupSetStatusAndTimestamp(schema)
                cursor.execute(query, [status, timestamp, groupname, membername, ownername])

def __insertOrUpdateBackupUserGroups(database, cursor, schema, userGroups):
    """Inserts or updates the given user-group relations into the ptp database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        userGroups: A list of UserGroup objects containing the user-group relations to be updated or inserted into the ptp database.
    
    Raises:
        An Exception is raised if the insertion was incorrect.
    """
    if userGroups != None:
        for userGroup in userGroups:
            username = userGroup.getUsername()
            groupname = userGroup.getGroupname()
            ownername = userGroup.getOwnername()
            userGroupInDb = selectUserGroup(database, cursor, schema, username, groupname, ownername)
            if userGroupInDb == None:
                isInserted = __insertUserGroup(database, cursor, schema, username, groupname, ownername)
                if isInserted == False:
                    raise Exception('insertError', 'usergroup ' + username + " for group " + groupname)

# delete

def deleteSelectedBackupsForUser(database, cursor, schema, backupContent):
    """Deletes specific parts of the backup for the given user from the ptp database.
    
    First, it deletes the users of the given backupContent.
    Then, it deletes all entries of the other tables that do not have any more a referenced entry in the user table.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        backupContent: A BackupContent object representing the part of the backup that should be deleted.
    
    Returns:
        Returns True if the deletion was successful, or False if an error occurred.
    """
    try:
        if backupContent != None:
            users = backupContent.getUsers()
            if users != None:
                for user in users:
                    username = user.getUsername()
                    Logger.info("delete selected backups for backup from " + backupContent.getFromUsername() + " and user " + username)
                    fromUsername = backupContent.getFromUsername()
                    if fromUsername == None:
                        query = __getQueryDeleteUserForNameForBackupFromNull(schema)
                        cursor.execute(query, [username])
                    else:
                        query = __getQueryDeleteUserForNameForBackupFrom(schema)
                        cursor.execute(query, [username, fromUsername])
                    # delete redundant user-groups
                    if __selectUserForUsername(database, cursor, schema, username) == None:
                        query = __getQueryDeleteUserGroup(schema)
                        cursor.execute(query, [username])
                
                # delete redundant groups, members and messages
                __deleteRedundantData(database, cursor, schema)
                database.commit()
            return True
        else:
            Logger.info("Error: backupcontent is none in delete selected backup")
    except:
        Logger.info("Error: unable to delete selected backup")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    return False
        
def deleteAllBackupsForUser(database, cursor, schema, fromUsername):
    """Deletes all backups for the given user from the ptp database.
    
    First, it deletes all users the given user is responsible of.
    Then, it deletes all entries of the other tables that do not have any more a referenced entry in the user table.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        fromUsername: The username for which the whole backup should be deleted.
    
    Returns:
        Returns True if the deletion was successful, or False if an error occurred.
    """
    try:
        if fromUsername == None:
            Logger.info("delete all backups for userfrom None")
        else:                        
            Logger.info("delete all backups for userfrom " + fromUsername)
        #delete users
        if fromUsername == None:
            query = __getQueryDeleteUserForBackupNull(schema)
            cursor.execute(query)
        else:
            query = __getQueryDeleteUserForBackup(schema)
            cursor.execute(query, [fromUsername])
        #delete redundant groups or groupdetails
        __deleteRedundantData(database, cursor, schema)
        database.commit()
        return True
    except:
        Logger.info("Error: unable to delete backups for name")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    return False



##########
# SELECT #
##########

# user

def selectUserImResponsibleForUsername(database, cursor, schema, username):
    """Selects a specific user from the database where the current user is responsible of.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String representing the name of the searched user.
    
    Returns:
        A User object that contains the user corresponding to the username is returned or None if an error occurred.
    """
    try:
        if username != None and len(username) > 0:
            query = __getQuerySelectUserImResponsibleForUsername(schema)
            return __selectUser(database, cursor, schema, query, [username])
        else:
            Logger.info("Error: username is None or has length=0 in selectUserForUsername")
    except:
        Logger.info("Error: unable to fetch user for name")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def __selectUserForUsername(database, cursor, schema, username):
    """Selects a specific user from the database where the current user is responsible of.
    
    Retrieves the query for the selection, executes it and returns the fetched result.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String representing the name of the searched user.
    
    Returns:
        A User object that contains the user corresponding to the username is returned or None if an error occurred.
    """
    try:
        if username != None and len(username) > 0:
            query = __getQuerySelectUserForUsername(schema)
            return __selectUser(database, cursor, schema, query, [username])
        else:
            Logger.info("Error: username is None or has length=0 in selectUserForUsername")
    except:
        Logger.info("Error: unable to fetch user for name")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def __selectUser(database, cursor, schema, query, parameter):
    """Selects a specific user from the database where the current user is responsible of.
    
    Retrieves the query for the selection, executes it and returns the fetched result.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        query: A String representing the query that should be executed.
        parameter: A list of Strings containing the parameters for the query.
    
    Returns:
        A User object that contains the user corresponding to the username is returned or None if an error occurred.
    """
    try:
        cursor.execute(query, parameter)
        result = cursor.fetchone()
        if (result != None) and (len(result) >= 5):
            uName = result[0]
            dName = result[1]
            pw = result[2]
            backupFrom = result[3]
            timestamp = float(result[4])
            user = User(uName, dName, pw, timestamp, backupFrom)
            msgs = selectMessagesForUsername(database, cursor, schema, uName)
            user.setMessages(msgs)
            return user
    except:
        Logger.info("Error: unable to fetch user for name")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def __selectUsersForBackup(database, cursor, schema, fromUsername):
    """Returns all users that are stored as backup from the given fromUsername in the database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        fromUsername: A String representing the user name of the user where the backuped users should be returned.

    Returns:
        A list of User objects representing the users backuped for the given username is returned.
    """
    try:
        if fromUsername == None:
            query = __getQuerySelectUsersForBackupNull(schema)
            cursor.execute(query)
        else:
            query = __getQuerySelectUsersForBackup(schema)
            cursor.execute(query, [fromUsername])
        users = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            uName = result[0]
            dName = result[1]
            pw = result[2]
            backup_from = result[3]
            timestamp = float(result[4])
            u = User(uName, dName, pw, timestamp, backup_from)
            users.append(u)
        for user in users:
            msgs = selectMessagesForUsername(database, cursor, schema, user.getUsername())
            user.setMessages(msgs)
        return users
    except:
        Logger.info("Error: unable to get users for backupname")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectUserGroup(database, cursor, schema, username, groupname, ownername):
    """Selects a specific user-group relation from the ptp database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String representing the user name.
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
    
    Returns:
        A UserGroup object containing the corresponding user-group relation or None if an error occurred.
    """
    try:
        if username != None and len(username) > 0 and groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0:
            query = __getQuerySelectUserGroup(schema)
            cursor.execute(query, [groupname, username, ownername])
            result = cursor.fetchone()
            if (result != None) and (len(result) >= 2):
                uName = result[0]
                gName = result[1]
                oName = result[2]
                return UserGroup(uName, gName, oName)
        else:
            Logger.info("Error: username is None or has length=0 in selectUserGroup")
    except:
        Logger.info("Error: unable to get usergroup for username: " + username + " and groupname: " + groupname)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectMessagesForUsername(database, cursor, schema, username, delete=False):
    """Selects all messages for a specific username.
    
    Retrieves all message ids belonging to the given user.
    Then, it retrieves all key-value pairs of such a message,
    constructs the original message out of it and adds it to the return list. 
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String representing the user name.
        delete: A boolean value, True if the messages should be deleted, otherwise False. Default False.
    
    Returns:
        A list of MemberStatusChangedMessage objects belonging to the given user name or None if an error occurred.
    """
    try:
        messages = []
        if username != None and len(username) > 0:
            query = __getQuerySelectMessageIdsForUsername(schema)
            cursor.execute(query, [username])
            msgIds = []
            while 1:
                result = cursor.fetchone()
                if result == None:
                    break
                Logger.info("found message for " + username)
                msgId = str(result[0])
                msgIds.append(msgId)
            if len(msgIds) == 0:
                messages = None
            else:
                for msgId in msgIds:
                    keyValues = {}
                    query = __getQuerySelectKeyValuesForMessageId(schema)
                    cursor.execute(query, [msgId])
                    while 1:
                        result = cursor.fetchone()
                        if result == None:
                            break
                        key = result[0]
                        value = result[1]
                        Logger.info("found key-value: " + key + "-" + value)
                        keyValues[key] = value
                    msg = CustodyModel.constructMessage(keyValues, msgId)
                    messages.append(msg)
                if delete:
                    query = __getQueryDeleteMessageIds(schema, len(msgIds))
                    cursor.execute(query, msgIds)
                    database.commit()
            return messages
    except:
        Logger.info("Error: unable to add messages to user")
        Logger.info(traceback.format_exc())
        if delete:
            database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def __selectRedundantMessages(database, cursor, schema):
    """Returns all groups that are no longer needed in the database.
    
    Database, cursor and schema are given as arguments.
    It returns all groups that have no relation to any user in the database.
    Returns the list of redundant groupnames as a string of the form (<name1>,<name2>,...)."""
    try:
        query = __getQuerySelectRedundantMessages(schema)
        cursor.execute(query)
        messageIdList = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            messageIdList.append(result[0])
        return messageIdList
    except:
        Logger.info("Error: unable to get group details for groupname")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None 

# group

def selectGroupsForUsername(database, cursor, schema, username):
    """Selects certain groups based on the user name that is a member of the group (has a user-group relation).
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String representing the user name.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    try:
        if username != None and len(username) > 0:
            query = __getQuerySelectGroupsForUsername(schema)
            return __selectGroups(database, cursor, schema, query, [username])
        else:
            Logger.info("Error: username is None or has length=0 in selectGroupsForUsername")
    except:
        Logger.info("Error: unable to get groups for user")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None 

def selectGroupsForOwnername(database, cursor, schema, ownername):
    """Selects certain groups based on the owner name.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        ownername: A String representing the name of the owner.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    try:
        if ownername != None and len(ownername) > 0:
            query = __getQuerySelectGroupForOwnername(schema)
            groups = __selectGroups(database, cursor, schema, query, [ownername])
            return groups
        else:
            Logger.info("Error: groupname is None or has length=0 in selectGroupForGroupname")
    except:
        Logger.info("Error: unable to fetch group for groupname")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectGroupForGroupnameAndOwnername(database, cursor, schema, groupname, ownername):
    """Selects certain groups based on the group name and owner name.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        groupname: A String representing the name of the group.
        ownername: A String representing the name of the owner.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    try:
        if groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0:
            query = __getQuerySelectGroupForGroupnameAndOwnername(schema)
            groups = __selectGroups(database, cursor, schema, query, [groupname, ownername])
            if groups != None and len(groups) > 0:
                return groups[0]
        else:
            Logger.info("Error: groupname is None or has length=0 in selectGroupForGroupname")
    except:
        Logger.info("Error: unable to fetch group for groupname")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectGroupsForDetails(database, cursor, schema, details):
    """Selects certain groups based on certain details.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        details: A dictionary of (String, String) containing the details (column name, column value).
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    try:
        if len(details.items()) > 0:
            query = __getQuerySelectGroupsForDetails(schema, details.keys())
            return __selectGroups(database, cursor, schema, query, details.values())
        else:
            Logger.info("Error: no details given in selectGroupForDetails")
    except:
        Logger.info("Error: unable to get group for for group details")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None 

def selectAllGroups(database, cursor, schema):
    """Selects all groups.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    try:
        groups = []
        query = __getQuerySelectAllGroups(schema)
        groups = __selectGroups(database, cursor, schema, query, [])
        return groups
    except:
        Logger.info("Error: unable to get all groups")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None 

def __selectGroups(database, cursor, schema, query, parameter):
    """Selects groups based on the given query and parameters.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        query: A String containing the query to be executed.
        parameter: A list containing the parameters needed when executing the query.
    
    Returns:
        A list of Strings containing the Group objects.
    """
    try:
        groups = []
        cursor.execute(query, parameter)
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            gName = result[0]
            dName = result[1]
            ownername = result[2]
            description = result[3]
            isActive = True if result[4] == 1 else False
            g = Group(gName, ownername, dName, description, None, isActive)
            groups.append(g)
        for g in groups:
            __addGenresToGroup(database, cursor, schema, g)
        for g in groups:
            __addMembersToGroup(database, cursor, schema, g)
        return groups
    except:
        Logger.info("Error: unable to get groups for user")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None 

def __selectRedundantGroups(database, cursor, schema):
    """Selects the redundant groups that do not have any reference in the user-group table.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A list of Strings containing the redundant group names or None if an error occurred.
    """
    try:
        query = __getQuerySelectRedundantGroups(schema)
        cursor.execute(query)
        groupnameList = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            groupnameList.append(result[0])
        return groupnameList
    except:
        Logger.info("Error: unable to get group details for groupname")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None 

def __addGenresToGroup(database, cursor, schema, group):
    """Selects the genres for the specific group and adds them to the Group object.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        group: A Group object where the members should be added.
    
    Returns:
        The Group objects with the added genres is returned or None if an error occurred.
    """
    try:
        if group != None:
            groupname = group.getGroupname()
            ownername = group.getOwnername()
            if groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0:
                query = __getQuerySelectGenresForGroupnameAndOwnername(schema)
                cursor.execute(query, [groupname, ownername])
                while 1:
                    result = cursor.fetchone()
                    if result == None:
                        break
                    genre = Genre(result[0])
                    Logger.info("adding genre to group " + groupname + ":" + genre.getName())
                    group.addGenre(genre)
    except:
        Logger.info("Error: unable to add members to group")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None
    

def __addMembersToGroup(database, cursor, schema, group):
    """Selects the members for the specific group and adds them to the Group object.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        group: A Group object where the members should be added.
    
    Returns:
        The Group objects with the added members is returned or None if an error occurred.
    """
    try:
        if group != None:
            groupname = group.getGroupname()
            ownername = group.getOwnername()
            if groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0:
                query = __getQuerySelectMemberGroupsForGroupnameAndOwnername(schema)
                cursor.execute(query, [groupname, ownername])
                while 1:
                    result = cursor.fetchone()
                    if result == None:
                        break
                    membername = result[0]
                    status = result[1]
                    timestamp = float(result[2])
                    Logger.info("adding member to group " + groupname + ":" + membername + " (" + status + " - " + str(timestamp) + ")")
                    group.addMember(membername, status, timestamp)
    except:
        Logger.info("Error: unable to add members to group")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectGroupsWithMembersToConfirm(database, cursor, schema, ownername):
    """Selects the groups of a certain owner where at least one member has the status NOT_CONFIRMED.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        ownername: A String containing the owner name.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    try:
        groups = []
        query = __getQuerySelectGroupsWithMembersToConfirm(schema)
        groups = __selectGroups(database, cursor, schema, query, [ownername])
        for group in groups:
            membersToConfirm = group.getMembersByStatus(PTPModel.MemberStatus.NOT_CONFIRMED)
            group.setMembers(membersToConfirm)
        return groups
    except:
        Logger.info("Error: unable to select groups with members to confirm")
        Logger.info(traceback.format_exc())
    return None 

def selectPredefinedGenres(database, cursor, schema):
    """Selects all predefined genres.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A list of Genre objects is returned or None if an error occurred.
    """
    try:
        query = __getQuerySelectPredefinedGenres(schema)
        cursor.execute(query)
        genres = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            genre = Genre(result[0])
            genres.append(genre)
        return genres
    except:
        Logger.info("Error: unable to select all genre types")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectPredefinedGenreByName(database, cursor, schema, genreName):
    """Selects the predefined genre that corrsponds to the given genre name.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        genreName: A String containing the genre name.
    
    Returns:
        A list of Genre objects is returned or None if an error occurred.
    """
    try:
        query = __getQuerySelectPredefinedGenreByName(schema)
        cursor.execute(query, [genreName])
        result = cursor.fetchone()
        if result != None and len(result) >= 0:
            genre = Genre(result[0])
            return genre
    except:
        Logger.info("Error: unable to select all genre types")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectChatHistoryByPartnernameAndUsername(database, cursor, schema, partnername, username):
    """Selects all chat messages of a certain user with a certain partner.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        partnername: A String containing the partner name of the chat message.
        username: A String containing the user name of the chat message.
    
    Returns:
        A list of ChatMessage objects is returned or a void list if an error occurred.
    """
    chatMessages = []
    try:
        query = __getQuerySelectChatHistoryForPartnernameAndUsername(schema)
        cursor.execute(query, [username, partnername])
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            if len(result) == 7:
                #id does not matter
                #username does not matter
                senderName = result[2]
                #partnername does not matter for model
                message = result[4]
                status = result[5]
                timestamp = float(result[6])
                msg = PTPModel.ChatMessage(senderName, message, timestamp, status)
                chatMessages.append(msg)
    except:
        Logger.info("Error: unable to select chat history for " + username)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    if len(chatMessages) <= 0:
        chatMessages = None
    return chatMessages
        

############
## INSERT ##
############

# user

def insertUser(database, cursor, schema, user, commit=True):
    """Inserts the given User object into the database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        user: The User object to be inserted.
        commit: The boolean value is set to true if the insertion should be committed or not. Default True.
    
    Returns:
        True is returned if the insertion was successful, or False if an error occurred.
    """
    try:
        if user != None:
            uName = user.getUsername()
            dName = uName if (user.getDisplayname() == None) else user.getDisplayname()
            pwd = user.getPassword()
            backup_from = user.getBackupFromUsername()
#            pwd = "NULL" if (user.getPassword() == None) else user.getPassword()
#            backup_from = "NULL" if (user.getBackupFromUsername() == None) else user.getBackupFromUsername()
            timestamp = 0.0 if (user.getTimestamp() == None) else str(user.getTimestamp())
            query = __getQueryInsertUser(schema)
            #Logger.info(query)
            cursor.execute(query, [uName, dName, pwd, backup_from, timestamp])
            msgs = user.getMessages()
            insertMessages(database, cursor, schema, uName, msgs, False)
            if commit:
                database.commit()
            return True
        else:
            Logger.info("Error: user is None in insertUser")
    except:
        Logger.info("Error: unable to insert user")
        Logger.info(traceback.format_exc())
        if commit:
            database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False

def __insertUserGroup(database, cursor, schema, username, groupname, ownername, commit=True):
    """Inserts the given user-group relation into the database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        usenamer: A String containing the user name.
        groupname: A String containing the group name.
        ownername: A String containing the owner name.
        commit: The boolean value is set to true if the insertion should be committed or not. Default True.
    
    Returns:
        True is returned if the insertion was successful, or False if an error occurred.
    """
    try:
        if username != None and len(username) > 0 and groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0:
            query = __getQueryInsertUserGroup(schema)
            cursor.execute(query, [username, groupname, ownername])
            if commit:
                database.commit()
            return True
        else:
            Logger.info("Error: username or groupname is None in inserting usergroup")
    except:
        Logger.info("Error: unable to insert usergroup")
        Logger.info(traceback.format_exc())
        if commit:
            database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False 
        
def insertMessages(database, cursor, schema, username, messages, commit=True):
    """Inserts the given messages into the database.
    
    The given list of Message objects is inserted and related to the given username.
    All stored messages, that are not in the list, will be deleted.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String representing the user that has to be related with the messages.
        messages: A list of Message objects to be inserted.
        commit: The boolean value is set to true if the insertion should be committed or not. Default True.
    
    Returns:
        True is returned if the insertion was successful, or False if an error occurred.
    """
    try:
        if messages == None or len(messages) == 0:
            Logger.info("no messages given, delete all msg in db")
            query = __getQuerySelectMessageIdsForUsername(schema)
            cursor.execute(query, [username])
            msgIds = []
            while 1:
                result = cursor.fetchone()
                if result == None:
                    break
                msgId = str(result[0])
                msgIds.append(msgId)
            if len(msgIds) > 0:
                query = __getQueryDeleteMessageIds(schema, len(msgIds))
                cursor.execute(query, msgIds)
        else:
            Logger.info("insert messages:")
            messagesInDb = selectMessagesForUsername(database, cursor, schema, username)
            for msg in messages:
                insert = True
                if messagesInDb != None:
                    for msgInDb in messagesInDb:
                        if CustodyModel.isEqual(msg, msgInDb):
                            # if message is already in db (also processed is the same) do nothing
                            if msg.isProcessed() == msgInDb.isProcessed():
                                Logger.info("same msg already in db")
                                insert = False
                            # if same message is in db but processed is not the same delete the msg in the db and insert it new
                            else:
                                Logger.info("same msg already in db, but with other processed status, therefore delete it")
                                query = __getQueryDeleteMessageIds(schema, 1)
                                cursor.execute(query, [msgInDb.getId()])
                            break
                if insert:
                    query = __getQuerySelectAvailableMessageId(schema)
                    cursor.execute(query)
                    result = cursor.fetchone()
                    if result == None:
                        msgId = 0
                    else:
                        msgId = str(result[0])
                    Logger.info("insert msg number " + str(msgId))
                    keyValues = CustodyModel.deconstructMessage(msg)
                    if keyValues != None:
                        parameters = []
                        for (key, value) in keyValues.iteritems():
                            parameters.append(msgId)
                            parameters.append(key)
                            parameters.append(value)
                        query = __getQueryInsertMessagesIdKeyValue(schema, keyValues.__len__())
                        cursor.execute(query, parameters)
                        query = __getQueryInsertUserMessage(schema)
                        cursor.execute(query, [username, msgId])
        if commit:
            database.commit()
        return True
    except:
        Logger.info("Error: unable to insert messages")
        Logger.info(traceback.format_exc())
        if commit:
            database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False 
        
def insertChatMessage(database, cursor, schema, username, partnername, chatMessage):
    """Inserts the given chat message and relates it to the given user name and partner name.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String containing the user name.
        partnername: A String containing the user name of the chat partner.
        chatMessage: A String containing the chat message to be inserted.
    
    Returns:
        True is returned if the insertion was successful, False otherwise.
    """
    try:
        if username != None and len(username) > 0 and partnername != None and len(partnername) > 0 and chatMessage != None:
            query = __getQueryInsertChatMessage(schema)
            sendername = chatMessage.getSendername()
            message = chatMessage.getMessage()
            status = chatMessage.getStatus()
            timestamp = chatMessage.getTimestamp()
            cursor.execute(query, [username, sendername, partnername, message, status, timestamp])
            database.commit()
            return True
        else:
            Logger.info("Error: username or partnername or chatmessage is None in inserting chatmessage")
    except:
        Logger.info("Error: unable to insert chatmessage")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False 

def updateChatMessageForSendernameAndTimestamp(database, cursor, schema, sendername, timestamp, status):
    """Updates the status of the chat message, that corresponds to the given user name of the sender and the given timestamp.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        sendername: A String containing the user name of the sender.
        timestamp: A float value containing the exact timestamp of the sending.
        status: A String representing the new status the corresponding chat message should have.
    
    Returns:
        True is returned if the update was successful, False otherwise.
    """
    try:
        if sendername != None and timestamp != None and status != None:
            query = __getQueryUpdateChatMessageForSendernameAndTimestamp(schema)
            cursor.execute(query, [status, sendername, timestamp])
            database.commit()
            return True
        else:
            Logger.info("error: sendername or timestamp or status none in updateChatMessageForSendernameAndTimestamp")
    except:
        Logger.info("Error: unable to updateChatMessageForSendernameAndTimestamp")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return False
    
# group

def establishGroup(database, cursor, schema, group, commit=True):
    """Inserts the given Group object into the database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        group: The Group object to be inserted.
        commit: The boolean value is set to true if the insertion should be committed or not. Default True.
    
    Returns:
        PTPModel.ReturnType.SUCCESSFUL is returned if the insertion was successful,
        PTPModel.ReturnType.GROUP_ALREADY_EXISTS if the group does exist already,
        or PTPModel.ReturnType.ERROR if an error occurred.
    """
    try:
        isInserted = __insertGroup(database, cursor, schema, group)
        if isInserted == PTPModel.ReturnType.SUCCESSFUL:
            ownername = group.getOwnername()
            groupname = group.getGroupname()
            timestamp = utils.DateFormatter.formatTimeAsFloat()
            # insert user-group relation for owner
            query = __getQueryInsertUserGroup(schema)
            cursor.execute(query, [ownername, groupname, ownername])
            # insert member-group relation for owner, status is active
            query = __getQueryInsertMemberGroup(schema)
            cursor.execute(query, [ownername, groupname, ownername, PTPModel.MemberStatus.ACTIVE, timestamp])
            if commit:
                database.commit()
            return PTPModel.ReturnType.SUCCESSFUL
        else:
            Logger.info("error: group not inserted")
            return isInserted
    except:
        Logger.info("Error: unable to establish group")
        Logger.info(traceback.format_exc())
        if commit:
            database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return PTPModel.ReturnType.ERROR

def updateGroup(database, cursor, schema, group):
    """Updates the corresponding Group object of the database with the values of the given Group object.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        group: The Group object to be updated.
    
    Returns:
        PTPModel.ReturnType.SUCCESSFUL is returned if the update was successful, or PTPModel.ReturnType.ERROR if an error occurred.
    """
    try:
        if group != None:
            groupname = group.getGroupname()
            ownername = group.getOwnername()
            description = group.getDescription()
            displayname = group.getDisplayname()
            isActive = "1" if group.isActive() else "0"
            genres = group.getGenres()
            
            query = __getQueryUpdateGroupDetails(schema)
            cursor.execute(query, [displayname, description, isActive, groupname, ownername])
            
            if genres != None and len(genres) > 0:
                query = __getQueryDeleteGenresForGroupnameAndOwnername(schema)
                cursor.execute(query, [groupname, ownername])
                query = __getQueryInsertGenreGroup(schema)
                for genre in genres:
                    cursor.execute(query, [groupname, ownername, genre.getName()])

            database.commit()
            return PTPModel.ReturnType.SUCCESSFUL
        else:
            Logger.info("error: group non in update group")
            return PTPModel.ReturnType.ERROR
    except:
        Logger.info("Error: unable to establish group")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return PTPModel.ReturnType.ERROR

def __insertGroup(database, cursor, schema, group):
    """Inserts the given Group object into the database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        group: The Group object to be inserted.
    
    Returns:
        PTPModel.ReturnType.SUCCESSFUL is returned if the insertion was successful,
        PTPModel.ReturnType.GROUP_ALREADY_EXISTS if the group does exist already,
        or PTPModel.ReturnType.ERROR if an error occurred.
    """
    try:
        if group != None:
            groupname = group.getGroupname()
            ownername = group.getOwnername()
            if (groupname != None) and (len(groupname) > 0) and ownername != None and len(ownername) > 0:
                #check if group already exists
                groupInDb = selectGroupForGroupnameAndOwnername(database, cursor, schema, groupname, ownername)
                if groupInDb == None:
                    ownername = group.getOwnername()
                    if ownername != None and len(ownername) > 0:
                        displayname = group.getDisplayname() #if (group.getDisplayname() != None) else group.getGroupname()
                        description = group.getDescription() #if (group.getDescription() != None) else "NULL"
                        genres = group.getGenres() #if (group.getGenre() != None) else "NULL"
                        isActive = "1" if (group.isActive()) else "0"
                        query = __getQueryInsertGroup(schema)
                        cursor.execute(query, [groupname, displayname, ownername, description, isActive])
                        if genres != None and len(genres) > 0:
                            query = __getQueryInsertGenreGroup(schema)
                            for genre in genres:
                                cursor.execute(query, [groupname, ownername, genre.getName()])
                        members = group.getMembers()
                        if members != None:
                            for member in members:
                                query = __getQueryInsertMemberGroup(schema)
                                cursor.execute(query, [member.getName(), groupname, ownername, member.getStatus(), member.getTimestamp()])
                        return PTPModel.ReturnType.SUCCESSFUL
                    else:
                        Logger.info('error: owner is None or has length=0 in insertGroup')
                else:
                    Logger.info('error: group already exists in db in insertGroup')
                    return PTPModel.ReturnType.GROUP_ALREADY_EXISTS
            else:
                Logger.info('error: groupname is none or has length=0 in insertGroup')
        else:
            Logger.info('error: group is None in insertGroup')
    except:
        Logger.info("Error: unable to insert group")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return PTPModel.ReturnType.ERROR


def insertMemberGroup(database, cursor, schema, membername, groupname, status, timestamp, ownername, insertGroupIfNotExisting):
    """Inserts the given group-member relastion into the database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        membername: A String containing the member name.
        groupname: A String containing the group name.
        status: A String containing the status of the membership.
        timestamp: A float value containing the exact timestamp of the insertion.
        ownername: A String containing the owner name.
        insertGroupIfNotExisting: A boolean value, True if the group should be created if not existing, False otherwise.
    
    Returns:
        PTPModel.MemberStatusChangedReturnType.SUCCESSFUL is returned if the insertion was successful,
            PTPModel.MemberStatusChangedReturnType.ERROR if an error occurred,
            PTPModel.MemberStatusChangedReturnType.GROUP_NOT_EXISTING if the group to update does not exist in the database,
            or PTPModel.MemberStatusChangedReturnType.ALREADY_MEMBER if the membership does already exist.
    """
    try:
        if membername != None and len(membername) > 0 and groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0:
            #check if member_group is not already in db
            groupInDb = selectGroupForGroupnameAndOwnername(database, cursor, schema, groupname, ownername)
            if groupInDb == None or groupInDb.getOwnername() != ownername:
                if insertGroupIfNotExisting:
                    group = Group(groupname, ownername)
                    group.addMember(membername, status, timestamp)
                    isInserted = __insertGroup(database, cursor, schema, group)
                    if isInserted == PTPModel.MemberStatusChangedReturnType.SUCCESSFUL:
                        database.commit()
                        return PTPModel.MemberStatusChangedReturnType.SUCCESSFUL
                    else:
                        database.rollback()
                        return PTPModel.MemberStatusChangedReturnType.ERROR
                else:
                    return PTPModel.MemberStatusChangedReturnType.GROUP_NOT_EXISTING
            
            if groupInDb.containsMember(membername):
                return PTPModel.MemberStatusChangedReturnType.ALREADY_MEMBER
            
            query = __getQueryInsertMemberGroup(schema)
            cursor.execute(query, [membername, groupname, ownername, status, timestamp])
            database.commit()
            return PTPModel.MemberStatusChangedReturnType.SUCCESSFUL
        else:
            Logger.info("Error: membername, groupname or ownername is None in inserting membergroup")
    except:
        Logger.info("Error: unable to insert members for group")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return PTPModel.MemberStatusChangedReturnType.ERROR

def updateMemberGroup(database, cursor, schema, membername, groupname, ownername, status, timestamp):
    """Updates the corresponding group-member relation of the database with the given values.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        membername: A String containing the member name.
        groupname: A String containing the group name.
        ownername: A String containing the owner name.
        status: A String containing the status of the membership.
        timestamp: A float value containing the exact timestamp of the membership creation.
    
    Returns:
        PTPModel.MemberStatusChangedReturnType.SUCCESSFUL is returned if the insertion was successful,
            PTPModel.MemberStatusChangedReturnType.ERROR if an error occurred,
            or PTPModel.MemberStatusChangedReturnType.GROUP_NOT_EXISTING if the group to update does not exist in the database.
    """
    try:
        if membername != None and len(membername) > 0 and groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0:
            #check if member_group is not already in db
            groupInDb = selectGroupForGroupnameAndOwnername(database, cursor, schema, groupname, ownername)
            if groupInDb != None:
                if groupInDb.containsMember(membername):
                    #insert member_group relations
                    if timestamp != None and timestamp >= 0:
                        query = __getQueryUpdateMemberGroupSetStatusAndTimestamp(schema)
                        cursor.execute(query, [status, timestamp, groupname, membername, ownername])
                    else:
                        query = __getQueryUpdateMemberGroupSetStatus(schema)
                        cursor.execute(query, [status, groupname, membername, ownername])
                    database.commit()
                    return PTPModel.MemberStatusChangedReturnType.SUCCESSFUL
                else:
                    Logger.info("Error: group " + groupname + " does not contain member " + membername)
            else:
                Logger.info("Error: group " + groupname + " where member " + membername + " should be inserted is not in db")
                database.rollback()
                return PTPModel.MemberStatusChangedReturnType.GROUP_NOT_EXISTING
        else:
            Logger.info("Error: membername or groupname is None in inserting membergroup")
    except:
        Logger.info("Error: unable to insert members for group")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return PTPModel.MemberStatusChangedReturnType.ERROR

def insertUserGroupAndGroup(database, cursor, schema, username, groupname, ownername):
    """Inserts the given user-group relation into the database, and, if not already present, it inserts the group (only group name and owner).
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        membername: A String containing the member name.
        groupname: A String containing the group name.
        ownername: A String containing the owner name.
    
    Returns:
        True is returned if the insertion was successful, False otherwise.
    """
    try:
        if username != None and len(username) > 0 and groupname != None and len(groupname) > 0 and ownername != None and len(ownername) > 0:
            group = selectGroupForGroupnameAndOwnername(database, cursor, schema, groupname, ownername)
            if group == None:
                query = __getQueryInsertGroup(schema)
                cursor.execute(query, [groupname, "", ownername, "", None])
            __insertUserGroup(database, cursor, schema, username, groupname, ownername, False)
            database.commit()
            return True
        else:
            Logger.info("Error: membername or groupname or ownername is None in inserting usergroup")
    except:
        Logger.info("Error: unable to insert user for group")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False

def setIsActiveGroupForGroupnameAndOwnername(database, cursor, schema, groupname, ownername, isActive):
    """Updates the column "isActive" for the entry in the group table with the given group name and owner name.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        groupname: A String containing the group name.
        ownername: A String containing the owner name.
        isActive: Boolean value, True if the group is still active, False otherwise. 
    
    Returns:
        True is returned if the update was successful, False otherwise.
    """
    try:
        if groupname != None and len(groupname) > 0:
            query = __getQueryUpdateGroupSetIsActive(schema)
            isActive = "0" if (isActive == False) else "1"
            cursor.execute(query, [isActive, groupname, ownername])
            database.commit()
            return True
        else:
            Logger.info("Error: groupname is None in set isActive for group " + groupname)
    except:
        Logger.info("Error: unable to set delete flag for group " + groupname)
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return False


############
## DELETE ##
############

def __deleteRedundantData(database, cursor, schema):
    """Deletes all data that is not needed anymore.
    
    First, it retrieves and deletes all groups, that do not have any reference to the user table.
    Then, it retrieves and deletes all messages, that do not have any reference to the user table.
    At last it checks the groups if the details (everything except group name and owner name)
    can be deleted (this is the case if the current user is not reponsible for the owner). 
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
    """
    try:
        #delete redundant groups
        redundantGroups = __selectRedundantGroups(database, cursor, schema)
        if redundantGroups != None and len(redundantGroups) > 0:
            query = __getQueryDeleteRedundantGroups(schema, len(redundantGroups))
            Logger.info(query)
            cursor.execute(query, redundantGroups)
    #        query = __getQueryDeleteRedundantMemberGroups(schema, redundantGroups)
    #        cursor.execute(query)
        redundantMessages = __selectRedundantMessages(database, cursor, schema)
        if redundantMessages != None and len(redundantMessages) > 0:
            query = __getQueryDeleteRedundantMessages(schema, len(redundantMessages))
            cursor.execute(query, redundantMessages)
            
        #delete redundant details of groups if owner is no more in db
        query = __getQueryUpdateRedundantGroupDetails(schema)
        cursor.execute(query)
    except:
        Logger.info("Error: unable delete redundant data")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
        

####################
## SELECT_QUERIES ##
####################

def __getQuerySelectUserImResponsibleForUsername(schema):
    """Creates a query for selecting a specific user from the database where the current user is responsible of.
    
    Select the user with the given username and only users where backupfrom = NULL
    because we want only users the current user is responsible of.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_USERNAME + " = %s"
    query += " and " + COLUMN_USER_BACKUP_FROM + " IS NULL"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectUserForUsername(schema):
    """Creates a query for selecting a user depending on the username.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_USERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectUsersForBackupNull(schema):
    """Creates a query for selecting all users the current user is responsible of.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_BACKUP_FROM + " IS NULL"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectUsersForBackup(schema):
    """Creates a query for selecting all users backuped for a specific user.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_BACKUP_FROM + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectBackupFromUsers(schema):
    """Creates a query for selecting all distinct values of the column backup_from of the user table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select distinct " + COLUMN_USER_BACKUP_FROM
    query += " from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_BACKUP_FROM + " IS NOT NULL"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectUserGroup(schema):
    """Creates a query for selecting a certain user-group relation based on the group name, owner name and user name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_USER_GROUP
    query += " where " + COLUMN_UG_GROUPNAME + " = %s"
    query += " and " + COLUMN_UG_USERNAME + " = %s"
    query += " and " + COLUMN_UG_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectMessageIdsForUsername(schema):
    """Creates a query for selecting all message ids for a given user.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select " + COLUMN_UM_MSGID
    query += " from " + schema + "." + TABLE_USER_MESSAGE
    query += " where " + COLUMN_UM_USERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectKeyValuesForMessageId(schema):
    """Creates a query for selecting all key-value pairs for a given message id.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select msg." + COLUMN_MSG_KEY + ", msg." + COLUMN_MSG_VALUE
    query += " from " + schema + "." + TABLE_MESSAGE + " msg"
    query += " where msg." + COLUMN_MSG_ID + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectAvailableMessageId(schema):
    """Creates a query for selecting the next available message id.
    
    Selects the message id by ordering descending and adding 1 to each value.
    So the first retrieved value is the next available message id.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select " + COLUMN_MSG_ID + "+1 from " + schema + "." + TABLE_MESSAGE
    query += " order by id desc"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectRedundantMessages(schema):
    """Creates a query for selecting redundant messages that do not have any reference in the user-message table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select distinct m." + COLUMN_MSG_ID
    query += " from " + schema + "." + TABLE_USER_MESSAGE + " um"
    query += " right join " + schema + "." + TABLE_MESSAGE + " m"
    query += " on um." + COLUMN_UM_MSGID + " = m." + COLUMN_MSG_ID
    query += " where um." + COLUMN_UM_MSGID + " is null"
    #Logger.ptpDBinfo(query)
    return query


def __getQuerySelectGroupForOwnername(schema):
    """Creates a query for selecting all groups corresponding to the given owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_GROUP
    query += " where " + COLUMN_GROUP_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectGroupForGroupnameAndOwnername(schema):
    """Creates a query for selecting a group for its group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_GROUP
    query += " where " + COLUMN_GROUP_GROUPNAME + " = %s"
    query += " and " + COLUMN_GROUP_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectGroupsForUsername(schema):
    """Creates a query for selecting all groups of a certain user.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select g.* from " + schema + "." + TABLE_GROUP + " g"
    query += " join " + schema + "." + TABLE_USER_GROUP + " ug on g." + COLUMN_GROUP_GROUPNAME + " = ug." + COLUMN_UG_GROUPNAME
    query += " and g." + COLUMN_GROUP_OWNERNAME + " = ug." + COLUMN_UG_OWNERNAME
    query += " where ug." + COLUMN_UG_USERNAME + " = %s" 
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectGroupsForDetails(schema, columns):
    """Creates a query for selecting a group based on some of its attributes.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        columns: A list of String containing the columns that have to match to certain values.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_GROUP + " g  where "
    for column in columns:
        query += "g." + column + " like %s and "
    query = query[0:len(query) - 5]
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectAllGroups(schema):
    """Creates a query for selecting all groups of the ptp database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_GROUP + " g"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectRedundantGroups(schema):
    """Creates a query for selecting redundant groups that do not have any reference in the user-group table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select g." + COLUMN_GROUP_GROUPNAME
    query += " from " + schema + "." + TABLE_USER_GROUP + " ug"
    query += " right join " + schema + "." + TABLE_GROUP + " g"
    query += " on ug." + COLUMN_UG_GROUPNAME + " = g." + COLUMN_GROUP_GROUPNAME
    query += " and ug." + COLUMN_UG_OWNERNAME + " = g." + COLUMN_GROUP_OWNERNAME
    query += " where ug." + COLUMN_UG_GROUPNAME + " IS NULL"
    #Logger.ptpDBinfo(query)
    return query 

def __getQuerySelectGenresForGroupnameAndOwnername(schema):
    """Creates a query for selecting all genres for a certain group based on its group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select gg." + COLUMN_GG_GENRE
    query += " from " + schema + "." + TABLE_GROUP_GENRE + " gg"
    query += " where gg." + COLUMN_GG_GROUPNAME + " = %s"
    query += " and gg." + COLUMN_GG_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query
    

def __getQuerySelectMemberGroupsForGroupnameAndOwnername(schema):
    """Creates a query for selecting member-group relations of a group based on the group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select mg." + COLUMN_MG_MEMBERNAME + ", mg." + COLUMN_MG_STATUS + ", mg." + COLUMN_MG_TIMESTAMP
    query += " from " + schema + "." + TABLE_MEMBER_GROUP + " mg"
    query += " where mg." + COLUMN_MG_GROUPNAME + " = %s"
    query += " and mg." + COLUMN_MG_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectGroupsWithMembersToConfirm(schema):
    """Creates a query for selecting all groups, where at least one member has the status NOT_CONFIRMED.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select distinct g.* from " + schema + "." + TABLE_GROUP + " g"
    query += " join " + schema + "." + TABLE_MEMBER_GROUP + " mg"
    query += " on mg." + COLUMN_MG_GROUPNAME + " = g." + COLUMN_GROUP_GROUPNAME
    query += " and mg." + COLUMN_MG_OWNERNAME + " = g." + COLUMN_GROUP_OWNERNAME
    query += " where g." + COLUMN_GROUP_OWNERNAME + " = %s"
    query += " and mg." + COLUMN_MG_STATUS + " = 'NOT_CONFIRMED'"
    Logger.ptpDBinfo(query)
    print query
    return query

def __getQuerySelectPredefinedGenres(schema):
    """Creates a query for selecting all predefined genres.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_GENRE
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectPredefinedGenreByName(schema):
    """Creates a query for selecting a predefined genre based on its name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_GENRE
    query += " where " + COLUMN_GENRE_NAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQuerySelectChatHistoryForPartnernameAndUsername(schema):
    """Creates a query for selecting all chat messages of a certain user and a certain partner name
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_CHAT_HISTORY
    query += " where " + COLUMN_CH_USERNAME + " = %s and " + COLUMN_CH_PARTNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

####################
## UPDATE_QUERIES ##
####################

def __getQueryUpdateRedundantGroupDetails(schema):
    """Creates a query for setting all columns of certain groups to NULL, except group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_GROUP
    query += " set " + COLUMN_GROUP_DESCRIPTION + " = NULL"
    query += ", " + COLUMN_GROUP_DISPLAYNAME + " = NULL"
    query += ", " + COLUMN_GROUP_IS_ACTIVE + " = NULL"
    query += " where " + COLUMN_GROUP_OWNERNAME + " not in"
    query += " ( select " + COLUMN_USER_USERNAME + " from " + schema + "." + TABLE_USER + " )"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryUpdateGroupDetails(schema):
    """Creates a query for setting all columns of certain groups, except group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_GROUP
    query += " set " + COLUMN_GROUP_DISPLAYNAME + " = %s"
    query += ", " + COLUMN_GROUP_DESCRIPTION + " = %s"
    query += ", " + COLUMN_GROUP_IS_ACTIVE + " = %s"
    query += " where " + COLUMN_GROUP_GROUPNAME + " = %s"
    query += " and " + COLUMN_GROUP_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryUpdateGroupSetIsActive(schema):
    """Creates a query for setting the column active for a certain group based on its group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_GROUP
    query += " set " + COLUMN_GROUP_IS_ACTIVE + " = %s"
    query += " where " + COLUMN_GROUP_GROUPNAME + " = %s"
    query += " and " + COLUMN_GROUP_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryUpdateMemberGroupSetStatus(schema):
    """Creates a query for setting the status of a certain member-group relation based on the group name, owner name and member name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_MEMBER_GROUP
    query += " set " + COLUMN_MG_STATUS + " = %s"
    query += " where " + COLUMN_MG_GROUPNAME + " = %s"
    query += " and " + COLUMN_MG_MEMBERNAME + " = %s"
    query += " and " + COLUMN_MG_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryUpdateMemberGroupSetStatusAndTimestamp(schema):
    """Creates a query for setting the status and the timestamp of a certain member-group relation based on the group name, owner name and member name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_MEMBER_GROUP
    query += " set " + COLUMN_MG_STATUS + " = %s, "
    query += COLUMN_MG_TIMESTAMP + " = %s"
    query += " where " + COLUMN_MG_GROUPNAME + " = %s"
    query += " and " + COLUMN_MG_MEMBERNAME + " = %s"
    query += " and " + COLUMN_MG_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryUpdateUser(schema):
    """Creates a query for setting all column of a ceratin user, except the username, based on the user name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_USER
    query += " set " + COLUMN_USER_DISPLAYNAME + " = %s, "
    query += COLUMN_USER_PW + " = %s, "
    query += COLUMN_USER_BACKUP_FROM + " = %s, "
    query += COLUMN_USER_TIMESTAMP + " = %s"
    query += " where " + COLUMN_USER_USERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryUpdateChatMessageForSendernameAndTimestamp(schema):
    """Creates a query for updating the status of a chat message based on the sender name and the timestamp.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_CHAT_HISTORY
    query += " set " + COLUMN_CH_STATUS + " = %s"
    query += " where " + COLUMN_CH_SENDERNAME + " = %s"
    query += " and " + COLUMN_CH_TIMESTAMP + " = %s"
    #Logger.ptpDBinfo(query)
    return query

####################
## INSERT_QUERIES ##
####################

def __getQueryInsertUser(schema):
    """Creates a query for inserting a user.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_USER
    query += " (" + COLUMN_USER_USERNAME + "," + COLUMN_USER_DISPLAYNAME + "," + COLUMN_USER_PW + "," + COLUMN_USER_BACKUP_FROM + "," + COLUMN_USER_TIMESTAMP + ")"
    query += " values (%s, %s, %s, %s, %s)"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryInsertGroup(schema):
    """Creates a query for inserting a group.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_GROUP
    query += " (" + COLUMN_GROUP_GROUPNAME + "," + COLUMN_GROUP_DISPLAYNAME + "," + COLUMN_GROUP_OWNERNAME 
    query += "," + COLUMN_GROUP_DESCRIPTION + "," + COLUMN_GROUP_IS_ACTIVE + ")"
    query += " values (%s, %s, %s, %s, %s)"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryInsertGenreGroup(schema):
    """Creates a query for inserting a group-genre relation.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_GROUP_GENRE
    query += " (" + COLUMN_GG_GROUPNAME + ", " + COLUMN_GG_OWNERNAME + ", " + COLUMN_GG_GENRE + ")"
    query += " values (%s, %s, %s)"
    #Logger.ptpDBinfo(query)
    return query
    
def __getQueryInsertUserGroup(schema):
    """Creates a query for inserting a user-group relation.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_USER_GROUP
    query += " (" + COLUMN_UG_USERNAME + "," + COLUMN_UG_GROUPNAME + "," + COLUMN_UG_OWNERNAME + ")"
    query += " values (%s, %s, %s)"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryInsertMemberGroup(schema):
    """Creates a query for inserting a member-group relation.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_MEMBER_GROUP
    query += " (" + COLUMN_MG_MEMBERNAME + ", " + COLUMN_MG_GROUPNAME + ", " + COLUMN_MG_OWNERNAME + ", " + COLUMN_MG_STATUS + ", " + COLUMN_MG_TIMESTAMP + ")"
    query += " values (%s, %s, %s, %s, %s)"
    #Logger.ptpDBinfo(query)
    return query


def __getQueryInsertMessagesIdKeyValue(schema, keyValueSize):
    """Creates a query for inserting several key-value pairs of a message.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_MESSAGE
    query += " (" + COLUMN_MSG_ID + "," + COLUMN_MSG_KEY + "," + COLUMN_MSG_VALUE + ")"
    query += " values "
    for i in range(0, keyValueSize):
        query += "(%s, %s, %s), "
    query = query[0:len(query) - 2]
    #Logger.ptpDBinfo(query)
    return query

def __getQueryInsertUserMessage(schema):
    """Creates a query for inserting a user-message relation.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_USER_MESSAGE
    query += " (" + COLUMN_UM_USERNAME + "," + COLUMN_UM_MSGID + ")"
    query += " values (%s, %s)"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryInsertChatMessage(schema):
    """Creates a query for inserting a chat message.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_CHAT_HISTORY
    query += " (" + COLUMN_CH_USERNAME + "," + COLUMN_CH_SENDERNAME + "," + COLUMN_CH_PARTNERNAME + "," + COLUMN_CH_MESSAGE + "," + COLUMN_CH_STATUS + "," + COLUMN_CH_TIMESTAMP + ")"
    query += " values (%s,%s,%s,%s,%s,%s)"
    #Logger.ptpDBinfo(query)
    return query

###################
## DELTE_QUERIES ##
###################

def __getQueryDeleteUserForNameForBackupFromNull(schema):
    """Creates a query for deleting a certain user based on its user name where the current user is responsible of (backup_from is NULL).
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_USERNAME + " = %s"
    query += " and " + COLUMN_USER_BACKUP_FROM + " IS NULL"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryDeleteUserForNameForBackupFrom(schema):
    """Creates a query for deleting a user based on the user name and the backup_from user name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_USERNAME + " = %s"
    query += " and " + COLUMN_USER_BACKUP_FROM + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryDeleteUserForBackupNull(schema):
    """Creates a query for deleting all users where the current user is responsible of (backup_from is NULL)
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_BACKUP_FROM + " IS NULL"
    #Logger.ptpDBinfo(query)
    return query
    
def __getQueryDeleteUserForBackup(schema):
    """Creates a query for deleting all users where a certain user is responsible of.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_USER
    query += " where " + COLUMN_USER_BACKUP_FROM + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryDeleteGenresForGroupnameAndOwnername(schema):
    """Creates a query for deleting all genres of a certain group based on the group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_GROUP_GENRE
    query += " where " + COLUMN_GG_GROUPNAME + " = %s"
    query += " and " + COLUMN_GG_OWNERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryDeleteMessageIds(schema, idSize):
    """Creates a query for deleting the key-value pairs of certain messages based on their id. 
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        idSize: An integer value indicating the number of messages where all key-value pairs should be deleted.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_MESSAGE
    query += " where " + COLUMN_MSG_ID + " in ("
    for i in range(0, idSize):
        query += "%s,"
    query = query[0:len(query) - 1]
    query += ")"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryDeleteRedundantMessages(schema, length):
    """Creates a query for deleting the messages with certain ids. 
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        length: An integer value indicating the number of messages to delete.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_MESSAGE
    query += " where " + COLUMN_MSG_ID + " in ("
    for i in range(0, length - 1):
        query += "%s,"
    query += "%s)"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryDeleteRedundantGroups(schema, length):
    """Creates a query for deleting all groups with a certain groupname.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        length: An integer value indicating the number of groups to delete.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_GROUP
    query += " where " + COLUMN_GROUP_GROUPNAME + " in ("
    for i in range(0, length - 1):
        query += "%s,"
    query += "%s)"
    #Logger.ptpDBinfo(query)
    return query

def __getQueryDeleteUserGroup(schema):
    """Creates a query for deleting all user-group relations based on the username.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_USER_GROUP
    query += " where " + COLUMN_UG_USERNAME + " = %s"
    #Logger.ptpDBinfo(query)
    return query

